<?php

/*
 * This file will perform the ajax for getting the accounts for the search results.
 * This is being used by admAccountSetup.php.
 * 
 * Created by: Peter Agno Jr.
 * Date created: November 16, 2011
 * 
 * In parameters: userId, firstName, middleName, lastName, status, departmentId, and positionId
 * Out parameters: account information for search results
 */

session_start();

// Start - Checker for those users who will just go to the page by typing directly in the url.
if ($_POST) {
    include('../includes/siteConfig.php');
    
    // connect to database
    $connect = mysql_connect($hostName,$rootName,$dBasePassword) or die ('Unable to connect!');
    mysql_select_db($dBaseName) or die ('Unable to select database!');

    // Get the data from post
    $userId = $_POST['userId'];
    $firstName = $_POST['firstName'];
    $middleName = $_POST['middleName'];
    $lastName = $_POST['lastName'];
    $status = $_POST['status'];
    $departmentId = $_POST['departmentId'];
    $positionId = $_POST['positionId'];
        
    // Prepare filter for userId
    $filterAccount = "";
    if ( $userId != "" ) {
        $filterAccount = "WHERE username LIKE '%" . $userId . "%' ";
    }
    
    // Prepare filter for firstName
    if ( $firstName != "" ) {
        if ( $filterAccount == "" ) {
            $filterAccount = "WHERE ";
        }
        else {
            $filterAccount = $filterAccount . "AND ";
        }
        
        $filterAccount = $filterAccount . "firstName LIKE '%" . $firstName . "%' ";
    }
    
    // Prepare filter for middleName
    if ( $middleName != "" ) {
        if ( $filterAccount == "" ) {
            $filterAccount = "WHERE ";
        }
        else {
            $filterAccount = $filterAccount . "AND ";
        }
        
        $filterAccount = $filterAccount . "middleName LIKE '%" . $middleName . "%' ";
    }
    
    // Prepare filter for lastName
    if ( $lastName != "" ) {
        if ( $filterAccount == "" ) {
            $filterAccount = "WHERE ";
        }
        else {
            $filterAccount = $filterAccount . "AND ";
        }
        
        $filterAccount = $filterAccount . "lastName LIKE '%" . $lastName . "%' ";
    }
    
    // Prepare filter for status
    if ( $status != "All" ) {
        if ( $filterAccount == "" ) {
            $filterAccount = "WHERE ";
        }
        else {
            $filterAccount = $filterAccount . "AND ";
        }
        
        $filterAccount = $filterAccount . "status = '" . $status . "' ";
    }
    
    // Prepare filter of departmentId and positionId
    $filterDeptPos = "";
//    if ( $departmentId != "All" ) {
//        $filterDeptPos = "AND DP.departmentId = '" . $departmentId . "' ";
//    }
    
    // Prepare filter for positionId
//    if ( $positionId != "All" ) {
//        $filterDeptPos = $filterDeptPos . "AND DP.positionId = '" . $positionId . "' ";
//    }
    
    // Prepare the query for getting the accounts
    $query =
        "
            -- Filter Department and Position
            SELECT FilteredAcc.userId as userId, username, firstName, middleName, lastName, FilteredAcc.status as status, D.departmentName as departmentName, P.positionName as positionName
            FROM
            
                -- Filter Account table
                (SELECT userId, username, firstName, middleName, lastName, status
                FROM Account
                ".$filterAccount.") as FilteredAcc, Account_Dept_Pos as ADP, Department_Position as DP, Department as D, Position as P
            
            WHERE FilteredAcc.userId = ADP.userId 
                AND ADP.deptPosId = DP.deptPosId 
                AND DP.departmentId = D.departmentId
                AND DP.positionId = P.positionId
                ".$filterDeptPos."
                    
            GROUP BY userId
            ORDER BY lastName ASC
        ";
    $result = mysql_query($query) or die ('Error in query: $query. ' . mysql_error());
    
    // Identify if there were querried accounts
    if ( mysql_num_rows($result) > 0 ) {
        while ($row = mysql_fetch_array($result)) {
            $jsondata = array();
            $jsondata['userId'] = $row['userId'];
            $jsondata['username'] = $row['username'];
            $jsondata['firstName'] = $row['firstName'];
            $jsondata['middleName'] = $row['middleName']; 
            $jsondata['lastName'] = $row['lastName'];
            $jsondata['status'] = $row['status'];
            $jsondata['departmentName'] = $row['departmentName']; 
            $jsondata['positionName'] = $row['positionName'];
            $feed[] = $jsondata;
        }
        
        echo json_encode($feed);
    }
    else {
        echo json_encode(0);
    }
    
    mysql_free_result($result);
    mysql_close($connect);
}   // End - Checker for those users who will just go to the page by typing directly in the url.
else {
    echo "You are not authorized to view this page. This incident will be reported immediately.";
}
?>
